﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_STOCKCHG(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_STOCKCHG(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_STOCKCHG(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_STOCKCHG(ref dwLocal, dwData);

            if (!flgExist)
            {
                dwLocal["ROWNID"] = GetSeq();
                InsertICC_STOCKCHG(dwLocal, 1);
            }
            UpdateICC_STOCKCHG(dwLocal);
        }
        public void InsertICC_STOCKCHG(DataRow dwData, int option=1)
        {

            /*INSERT INTO ICC_STOCKCHG
DELETE FROM ICC_STOCKCHG
GO
DROP TABLE ICC_STOCKCHG
GO
CREATE TABLE ICC_STOCKCHG
AS
--INSERT INTO ICC_STOCKCHG
SELECT * FROM (SELECT GABMANDANT AS MANDANT,GABLO AS LO ,GABLB AS LB ,GABBUCHDAT AS MDATE,GPBSPPART AS SPPART,GPBZWPART AS ZWPART,GPBART AS ART,GPBVAR AS VAR, GPBFARBE AS FARBE,GPBFARBE AS FINISH, W.GABBS AS BS,MAX(SCN) AS BSDES,SUM(GABKG*0.001) AS BSQTYFROM ICC_GABEW W JOIN GPBST ON GABPARTIE=GPBPARTIE JOIN ICC_GABBS B ON W.GABBS=B.GABBS WHERE  W.GABBS NOT IN ('61','62')  GROUP BY GABMANDANT,GABLO,GABLB,GABBUCHDAT,GPBSPPART,GPBZWPART,GPBART,GPBVAR,GPBFARBE,W.GABBS  ORDER BY 1,2,3,4,5,6,7) X WHERE NOT EXISTS (SELECT 1 FROM ICC_STOCKCHG Y  WHERE X.MANDANT=Y.MANDANT AND X.LO=Y.LO AND X.LB=Y.LB AND X.MDATE=Y.MDATE  AND X.SPPART=Y.SPPART AND X.ZWPART=Y.ZWPART  AND X.ART=Y.ART AND X.VAR=Y.VAR AND X.FARBE=Y.FARBE AND X.FINISH=Y.FINISH AND X.BS=Y.BS)*/
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO ICC_STOCKCHG SELECT * FROM (SELECT GABMANDANT AS MANDANT, GABLO AS LO, GABLB AS LB, GABBUCHDAT AS MDATE, GPSPPART AS SPPART, GPZWPART AS ZWPART, GPFBPART AS FBPART, GPBEM1 AS BEM1, GPGART AS ART, GPGVAR AS VAR, GPGFARBE AS FARBE, GPGFARBE AS FINISH, W.GABBS AS BS, MAX(SCN) AS BSDES, SUM(GABKG * 0.001) AS BSQTY FROM ICC_GABEW W JOIN GPDAT ON GABPARTIE = GPPARTIE JOIN ICC_GABBS B ON W.GABBS = B.GABBS WHERE W.GABBS NOT IN ('61', '62') GROUP BY GABMANDANT, GABLO, GABLB, GABBUCHDAT, GPSPPART, GPZWPART, GPFBPART, GPGART, GPGVAR, GPGFARBE, GPBEM1, W.GABBS ORDER BY 1, 2, 3, 4, 5, 6, 7) X WHERE NOT EXISTS (SELECT 1 FROM ICC_STOCKCHG Y WHERE X.MANDANT = Y.MANDANT AND X.LO = Y.LO AND X.LB = Y.LB AND X.MDATE = Y.MDATE AND X.FBPART = Y.FBPART AND X.BEM1 = Y.BEM1 AND X.SPPART = Y.SPPART AND X.ZWPART = Y.ZWPART AND X.ART = Y.ART AND X.VAR = Y.VAR AND X.FARBE = Y.FARBE AND X.FINISH = Y.FINISH AND X.BS = Y.BS)";          
                    break;
                default:
                    break;
            }            
            Connect2Proxy(or1);
        }
        public void DeleteICC_STOCKCHG(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_STOCKCHG"
                 + " WHERE GABBELEG<=:GABBELEG";
                    or1.Parameters.Add("GABBELEG", OracleType.NVarChar).Value = dwData["GABBELEG"].ToString();
                    break;
                default: 
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_STOCKCHG(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT MAX(GABBELEG) AS GABBELEG  FROM ICC_STOCKCHG"; ;                     
                    break;
                case 2:
                    or1.CommandText = "SELECT MAX(GABBELEG) AS GABBELEG  FROM ICC_STOCKCHG WHERE GABBUCHDAT<=:GABBUCHDAT"; ;
                    or1.Parameters.Add("GABBUCHDAT", OracleType.NVarChar).Value = dwData["GABBUCHDAT"].ToString();
                    break;  
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_STOCKCHG";
            Return = Result;
            return Return;
        }
        public void UpdateICC_STOCKCHG(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            #region====Command====
            or1.CommandText = "UPDATE ICC_STOCKCHG SET "
                + "GABBS=:GABBS,"
                + "EN=:EN,"
                + "SCN=:SCN "
                + " WHERE  "
                + "    ROWNID=:ROWNID ";
            #endregion
            #region====Parameters==== 
            or1.Parameters.Add("ROWNID", OracleType.NVarChar).Value = dwData["ROWNID"].ToString(); 
            or1.Parameters.Add("GABBS", OracleType.NVarChar).Value = dwData["GABBS"].ToString();
            or1.Parameters.Add("EN", OracleType.NVarChar).Value = dwData["EN"].ToString();
            or1.Parameters.Add("SCN", OracleType.NVarChar).Value = dwData["SCN"].ToString();
            #endregion
            Connect2Proxy(or1);
        }
    }
}
